package com.ruoyi.web.controller.system;

import com.ruoyi.common.core.domain.entity.RaBase;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.system.mapper.RaBaseMapper;
import jxl.Cell;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.record.RKRecord;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;

import javax.annotation.Resource;

/**
 * 从瑞安导出的excel，再导入到数据库中
 * @DATE : 2024/05/28 15:04
 */
@RestController
@RequestMapping("/raImport")
public class RaImportController {

    @Resource
    private RaBaseMapper raBaseMapper;

    @GetMapping("/import")
    public void raImport() throws Exception {
        Object[] obj = null;
        ArrayList<Object[]> list = new ArrayList<Object[]>();  // 定义ArrayList,包含数组类型
        String filePath = "C:/Users/admin/Desktop/桌面文件/云端精确投药/瑞安精确投药/瑞安22-24/第一波.xls";
        File file = new File(filePath);
        boolean exists = file.exists();
        // 定义Excel文件路径
        FileInputStream is = null;
        Workbook rwb = null;
        try {
            is = new FileInputStream(filePath); // 定义文本输入流
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        try {
            rwb = Workbook.getWorkbook(is); // 打开Workbook
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        Sheet sht = rwb.getSheet("Sheet1"); // 打开Workbook中Sheet1
        int col = sht.getColumns();  // 获得Excel列
        int row = sht.getRows();  // 获得Excel行
        Cell c1 = null;

        for (int i = 0; i < row; i++)  // 先将数据按行装入一个一维数组中， 然后将数组逐个加入到ArrayList
        {
            obj = new Object[col];

            for (int j = 0; j < col; j++) {
                c1 = sht.getCell(j, i);
                obj[j] = c1.getContents().toString();
            }
            list.add(obj);
        }


        RaBase rabase = new RaBase();
        List<RaBase> res = new ArrayList<>();
        obj = new Object[17];
        int count = 0;
        for (int k = 0; k < row; k++)   // 将ArrayList数据读出来，作为参数，批量INSERT到数据库
        {
            obj = list.get(k);
            rabase.setYqJsll(Double.valueOf(obj[0].toString()));
            rabase.setFit102(Double.valueOf(obj[1].toString()));
            rabase.setMit101T(Double.valueOf(obj[2].toString()));
            rabase.setAit101Ph(Double.valueOf(obj[3].toString()));
            rabase.setAit101T(Double.valueOf(obj[4].toString()));
            rabase.setJnyszd(Double.valueOf(obj[5].toString()));
            rabase.setJnysph(Double.valueOf(obj[6].toString()));
            rabase.setShsjtjll4(Double.valueOf(obj[7].toString()));
            rabase.setShsjtjll2(Double.valueOf(obj[8].toString()));
            rabase.setDitt11(Double.valueOf(obj[9].toString()));
            rabase.setCdc1ZdZ(Double.valueOf(obj[10].toString()));
            rabase.setCdc2ZdZ(Double.valueOf(obj[11].toString()));
            rabase.setYqJySsll(Double.valueOf(obj[12].toString()));
            rabase.setEqJySsll(Double.valueOf(obj[13].toString()));
            rabase.setYqTjlShow(Double.valueOf(obj[14].toString()));
            rabase.setEqTjlShow(Double.valueOf(obj[15].toString()));
            rabase.setDateTime(DateUtils.strToDate(obj[16].toString(), "MM/dd/yy HH:mm"));
            res.add(rabase);
            count++;
            if (count >= 100) {
                // raBaseMapper.batchInsert(res);
                res = new ArrayList<>();
                count = 0;
            }
        }

        if (!res.isEmpty()) {
            // raBaseMapper.batchInsert(res);
        }



    }
}
